﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using Oracle.DataAccess.Client;

namespace CrmApplication.Models
{
    public class BranchModels
	{
		#region RegionMappingBranch

		public decimal IdCompany { get; set; }

        public decimal IdBranch { get; set; }		
        public string BranchName { get; set; }

        public decimal IdCity{ get; set; }
        public string IdCity_val { get; set; }        
        public string CityName { get; set; }

        public decimal IdCounty { get; set; }
        public string CountyName { get; set; }

        public string Country { get; set; }

        public string Address { get; set; }
        public string AddressDetail { get; set; }

		#endregion

		#region RegionOracleConnect
		private static OracleConnection connect;

        static BranchModels()
        {
            string connString = System.Configuration.ConfigurationManager.ConnectionStrings["HomeOrcl"].ConnectionString;
            connect = new OracleConnection(connString);
        }

		#endregion

		#region RegionBranch
		/// <summary>
		/// get all branches by selected company
		/// </summary>
		/// <param name="idCompany"></param>
		/// <returns></returns>
		public static List<BranchModels> GetBranch(decimal idCompany)
        {
            string selectString = "select b.id_branch,b.branch_name,nc.city_name,nct.county_name,b.country,b.address,b.address_detail "
                                    + " from branch b join nomcity  nc on "
                                    + " b.id_city = nc.id_city "
                                    + " join nomcounty nct on "
                                    + " nc.id_county=nct.id_county "
                                    + " where b.id_company =:idCompany ";  

            OracleCommand loginComm = new OracleCommand(selectString);
            loginComm.Connection = connect;

            loginComm.Parameters.Add(":idCompany", idCompany);

            List<BranchModels> result = new List<BranchModels>();
            BranchModels result1 = null;
            try
            {
                connect.Open();
                using (OracleDataReader sdr = loginComm.ExecuteReader())
                {
                    while (sdr.Read())
                    {
                        result1 = new BranchModels();                        
                        result1.IdBranch = sdr.GetDecimal(0);
                        result1.BranchName = sdr.GetString(1);                        
                        result1.CityName = sdr.GetString(2);                        
                        result1.CountyName = sdr.GetString(3);
                        result1.Country = sdr.GetString(4);
                        result1.Address= sdr.GetString(5);
                        result1.AddressDetail = sdr.GetString(6);
                        result.Add(result1);
                    }
                }
            }
            finally
            {
                connect.Close();
            }
            return result;
        }

        /// <summary>
        /// get branch info for a selected row in grid(by branch id)
        /// </summary>
        /// <param name="idBranch"></param>
        /// <returns></returns>
        public static List<BranchModels> GetBranchById(decimal idBranch)
        {
            string selectString = "SELECT b.id_branch,b.branch_name,b.id_city,nc.city_name,b.country,b.address,b.address_detail" +
                                        " from branch b "+
                                        " LEFT JOIN nomcity nc on b.id_city =nc.id_city "+
                                        " where id_branch = :id";

            OracleCommand loginComm = new OracleCommand(selectString);
            loginComm.Connection = connect;

            loginComm.Parameters.Add(":id", idBranch);

            List<BranchModels> result = new List<BranchModels>();
            BranchModels result1 = null;

            try
            {
                connect.Open();
                using (OracleDataReader sdr = loginComm.ExecuteReader())
                {
                    while (sdr.Read())
                    {
                        result1 = new BranchModels();
                        result1.IdBranch = sdr.GetDecimal(0);
                        result1.BranchName = sdr.GetString(1);                        
                        result1.IdCity = sdr.GetDecimal(2);
                        result1.IdCity_val = sdr.GetString(3);
                        result1.Country = sdr.GetString(4);
                        result1.Address = sdr.GetString(5);
                        result1.AddressDetail = sdr.GetString(6);
                        result.Add(result1);
                    }
                }
            }
            finally
            {
                connect.Close();
            }
            return result;
        }

        /// <summary>
        /// update branch info
        /// </summary>
        /// <param name="branch"></param>
        /// <returns></returns>
        public static BranchModels EditBranch(BranchModels branch)
        {
            string selectString = "update branch set branch_name=:branchName,id_city=:idCity,country=:country,address=:address,address_detail=:addressDetail where id_branch=:idBranch";
                                 

            OracleCommand updateComm = new OracleCommand(selectString);
            updateComm.Connection = connect;
            updateComm.Parameters.Add(":branchName", branch.BranchName);
            updateComm.Parameters.Add(":idCity", branch.IdCity);
            updateComm.Parameters.Add(":country", branch.Country);
            updateComm.Parameters.Add(":address", branch.Address);
            updateComm.Parameters.Add(":addressDetail", branch.AddressDetail);
            updateComm.Parameters.Add(":idBranch", branch.IdBranch);

            BranchModels result = null;
            try
            {
                connect.Open();
                using (OracleDataReader sdr = updateComm.ExecuteReader())
                {
                    while (sdr.Read())
                    {
                        result = new BranchModels();
                        result.BranchName = sdr.GetString(0);
                        result.IdCity = sdr.GetDecimal(1);
                        result.Country = sdr.GetString(2);
                        result.Address = sdr.GetString(3);
                        result.AddressDetail = sdr.GetString(4);
                        result.IdBranch = sdr.GetDecimal(5);
                    }
                }
            }
            finally
            {
                connect.Close();
            }
            return result;
        }

        /// <summary>
        /// check if branch exists
        /// </summary>
        /// <param name="company"></param>
        /// <returns></returns>
        public static BranchModels CheckBranch(BranchModels branch)
        {
            string selectString = "select id_branch,branch_name from branch where branch_name=:branchName ";

            OracleCommand loginComm = new OracleCommand(selectString);
            loginComm.Connection = connect;

            loginComm.Parameters.Add(":branchName", branch.BranchName);

            BranchModels result = null;

            try
            {
                connect.Open();
                using (OracleDataReader sdr = loginComm.ExecuteReader())
                {
                    while (sdr.Read())
                    {
                        result = new BranchModels();
                        result.IdBranch = sdr.GetDecimal(0);
                        result.BranchName = sdr.GetString(1);
                    }
                }
            }
            finally
            {
                connect.Close();
            }
            return result;
        }

        /// <summary>
        /// chech if branch name exist (used for edit)
        /// </summary>
        /// <param name="branch"></param>
        /// <returns></returns>
        public static BranchModels CheckBranchEdit(BranchModels branch)
        {
            string selectString = "select id_branch,branch_name from branch"
                                    +" where branch_name = :branchName and id_company !=:idCompany";

            OracleCommand loginComm = new OracleCommand(selectString);
            loginComm.Connection = connect;

            loginComm.Parameters.Add(":branchName", branch.BranchName);
            loginComm.Parameters.Add(":idCompany", branch.IdCompany);

            BranchModels result = null;

            try
            {
                connect.Open();
                using (OracleDataReader sdr = loginComm.ExecuteReader())
                {
                    while (sdr.Read())
                    {
                        result = new BranchModels();
                        result.IdBranch = sdr.GetDecimal(0);
                        result.BranchName = sdr.GetString(1);
                        result.IdCompany = sdr.GetDecimal(2);
                    }
                }
            }
            finally
            {
                connect.Close();
            }
            return result;
        }

        /// <summary>
        /// add new branch
        /// </summary>
        /// <param name="company"></param>
        public static void AddBranch(BranchModels branch)
        {
            string insert = "INSERT INTO branch(branch_name,id_company,id_city,country,address,address_detail)" +
                            "VALUES (:branchName,:idCompany,:idCity,:country,:address,:addressDetail)";

            OracleCommand usercomm = new OracleCommand(insert);
            usercomm.Parameters.Add("branchName", branch.BranchName);
            usercomm.Parameters.Add("idCompany", branch.IdCompany);
            usercomm.Parameters.Add("idCity", branch.IdCity);
            usercomm.Parameters.Add("country", branch.Country);
            usercomm.Parameters.Add("address", branch.Address);
            usercomm.Parameters.Add("addressDetail", branch.AddressDetail);

            usercomm.Connection = connect;

            try
            {
                connect.Open();
                usercomm.ExecuteNonQuery();
            }
            finally
            {
                connect.Close();
            }
        }
		#endregion

		#region RegionJsonBranchArray
		/// <summary>
        /// get selected branch array for json
        /// </summary>
        /// <param name="idCompany"></param>
        /// <returns></returns>
        public static BranchContainer GetBranchArrayById(decimal idBranch)
        {
            BranchModels[] data = new BranchModels[20];
            data = BranchModels.GetBranchById(idBranch).ToArray();

            BranchContainer container = new BranchContainer()
            {
                Records = data,
                Count = data.Length
            };

            return container;
        }

        /// <summary>
        /// get branch list by company id
        /// </summary>
        /// <param name="idCompany"></param>
        /// <returns></returns>
        public static BranchContainer GetBranchArray(decimal idCompany)
        {
            BranchModels[] data = new BranchModels[20];
            data = BranchModels.GetBranch(idCompany).ToArray();

            BranchContainer container = new BranchContainer()
            {
                Records = data,
                Count = data.Length
            };

            return container;
		}

		#endregion
	}

    public class BranchContainer
    {
        public BranchModels[] Records { get; set; }
        public int Count { get; set; }
    }
}